﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for VYC06BO
    /// </summary>
    public class VYC06BO : DBConnectionBO
    {
        public VYC06BO()
            : base()
        {
            //
        }

        /// <summary>
        /// Lay danh sach cac du lieu trong VYC06 : Tin tuc
        /// </summary>
        /// <returns></returns>
        public List<ModelVYC06> GetAllList()
        {
            var list = new List<ModelVYC06>();
            const string query = "SELECT [Id],[IdSubLoaiChuyenMuc],[IdLoaiChuyenMuc],[TieuDe],[UniqueId],[AnhDaiDien],[TomTat],[NoiDung],[LuotXem],[TacGia],[LienKet],[Tag],[NgayCapNhat],[TrangThai],[HienthiPanoramanho] FROM [VYC-06]";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC06();

                model.Id = (dataTable.Rows[i][0] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][0]);
                model.IdSubLoaiChuyenMuc = (dataTable.Rows[i][1] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][1]);
                model.IdLoaiChuyenMuc = (dataTable.Rows[i][2] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][2]);
                model.TieuDe = (dataTable.Rows[i][3] == DBNull.Value) ? string.Empty : dataTable.Rows[i][3].ToString();
                model.UniqueId = (dataTable.Rows[i][4] == DBNull.Value) ? string.Empty : dataTable.Rows[i][4].ToString();
                model.AnhDaiDien = (dataTable.Rows[i][5] == DBNull.Value) ? string.Empty : dataTable.Rows[i][5].ToString();
                model.TomTat = (dataTable.Rows[i][6] == DBNull.Value) ? string.Empty : dataTable.Rows[i][6].ToString();
                model.NoiDung = (dataTable.Rows[i][7] == DBNull.Value) ? string.Empty : dataTable.Rows[i][7].ToString();
                model.LuotXem = (dataTable.Rows[i][8] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][8]);
                model.TacGia = (dataTable.Rows[i][9] == DBNull.Value) ? string.Empty : dataTable.Rows[i][9].ToString();
                model.LienKet = (dataTable.Rows[i][10] == DBNull.Value) ? string.Empty : dataTable.Rows[i][10].ToString();
                model.Tag = (dataTable.Rows[i][11] == DBNull.Value) ? string.Empty : dataTable.Rows[i][11].ToString();
                model.NgayCapNhat = (dataTable.Rows[i][12] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][12];
                model.TrangThai = (dataTable.Rows[i][13] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][13]);
                model.HienthiPanoramanho = (dataTable.Rows[i][14] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][14]);
                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Add new a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddData(ModelVYC06 model)
        {
            var query = "INSERT INTO [VYC-06]([IdSubLoaiChuyenMuc],[IdLoaiChuyenMuc],[TieuDe],[UniqueId],[AnhDaiDien],[TomTat],[NoiDung],[LuotXem],[TacGia],[LienKet],[Tag],[NgayCapNhat],[TrangThai],[HienthiPanoramanho]) VALUES (@IdSLCM, @IdLCM, @Tieude, @UniqueId, @Anhdaidien, @Tomtat, @Noidung, @Luotxem, @Tacgia, @Lienket, @Tag, GETDATE(), @Trangthai, @Hienthipanoramanho)";
            var sqlCommand = new SqlCommand(query, connection);
            // Add Parameter to SQL Command
            sqlCommand.Parameters.Add("@IdSLCM", SqlDbType.Int).Value = model.IdSubLoaiChuyenMuc;
            sqlCommand.Parameters.Add("@IdLCM", SqlDbType.Int).Value = model.IdLoaiChuyenMuc;
            sqlCommand.Parameters.Add("@Tieude", SqlDbType.NVarChar).Value = model.TieuDe;
            sqlCommand.Parameters.Add("@UniqueId", SqlDbType.VarChar).Value = model.UniqueId;
            sqlCommand.Parameters.Add("@Anhdaidien", SqlDbType.VarChar).Value = model.AnhDaiDien;
            sqlCommand.Parameters.Add("@Tomtat", SqlDbType.NVarChar).Value = model.TomTat;
            sqlCommand.Parameters.Add("@Noidung", SqlDbType.NText).Value = model.NoiDung;
            sqlCommand.Parameters.Add("@Luotxem", SqlDbType.Int).Value = model.LuotXem;
            sqlCommand.Parameters.Add("@Tacgia", SqlDbType.NVarChar).Value = model.TacGia;
            sqlCommand.Parameters.Add("@Lienket", SqlDbType.NVarChar).Value = model.LienKet;
            sqlCommand.Parameters.Add("@Tag", SqlDbType.NVarChar).Value = model.Tag;
            sqlCommand.Parameters.Add("@Trangthai", SqlDbType.Bit).Value = model.TrangThai;
            sqlCommand.Parameters.Add("@Hienthipanoramanho", SqlDbType.Bit).Value = model.HienthiPanoramanho;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Edit a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int EditData(ModelVYC06 model)
        {
            var query = "UPDATE [VYC-06] SET " +
                        "IdSubLoaiChuyenMuc = " + model.IdSubLoaiChuyenMuc +
                        ",IdLoaiChuyenMuc = " + model.IdLoaiChuyenMuc +
                        ",TieuDe = N'" + model.TieuDe + "'" +
                        ",UniqueId = '" + model.UniqueId + "'" +
                        ",AnhDaiDien = '" + model.AnhDaiDien + "'" +
                        ",TomTat = N'" + model.TomTat + "'" +
                        ",NoiDung = N'" + model.NoiDung + "'" +
                        ",LuotXem = " + model.LuotXem +
                        ",TacGia = N'" + model.TacGia + "'" +
                        ",LienKet = N'" + model.LienKet + "'" +
                        ",Tag = N'" + model.Tag + "'" +
                        ",NgayCapNhat = GETDATE(), TrangThai = " + model.TrangThai +
                        ",HienthiPanoramanho = " + model.HienthiPanoramanho +
                        " WHERE Id = '" + model.Id + "'";
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Update luot xem
        /// </summary>
        /// <param name="id"></param>
        /// <param name="luotxem"></param>
        /// <returns></returns>
        public int UpdateView(int id, int luotxem)
        {
            const string query = "UPDATE [VYC-06] SET LuotXem = @luotxem WHERE Id = @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@luotxem", SqlDbType.Int).Value = luotxem;
            sqlCommand.Parameters.Add("@id", SqlDbType.Int).Value = id;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int DeleteData(ModelVYC06 model)
        {
            var query = "DELETE FROM [VYC-06] " +
                        " WHERE Id = " + model.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        public List<ModelVYC06> GetById(int id)
        {
            var list = new List<ModelVYC06>();
            var query = "SELECT [Id],[IdSubLoaiChuyenMuc],[IdLoaiChuyenMuc],[TieuDe],[UniqueId],[AnhDaiDien],[TomTat],[NoiDung],[LuotXem],[TacGia],[LienKet],[Tag],[NgayCapNhat],[TrangThai],[HienthiPanoramanho] FROM [VYC-06] WHERE Id=" + id;
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC06();

                model.Id = (dataTable.Rows[i][0] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][0]);
                model.IdSubLoaiChuyenMuc = (dataTable.Rows[i][1] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][1]);
                model.IdLoaiChuyenMuc = (dataTable.Rows[i][2] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][2]);
                model.TieuDe = (dataTable.Rows[i][3] == DBNull.Value) ? string.Empty : dataTable.Rows[i][3].ToString();
                model.UniqueId = (dataTable.Rows[i][4] == DBNull.Value) ? string.Empty : dataTable.Rows[i][4].ToString();
                model.AnhDaiDien = (dataTable.Rows[i][5] == DBNull.Value) ? string.Empty : dataTable.Rows[i][5].ToString();
                model.TomTat = (dataTable.Rows[i][6] == DBNull.Value) ? string.Empty : dataTable.Rows[i][6].ToString();
                model.NoiDung = (dataTable.Rows[i][7] == DBNull.Value) ? string.Empty : dataTable.Rows[i][7].ToString();
                model.LuotXem = (dataTable.Rows[i][8] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][8]);
                model.TacGia = (dataTable.Rows[i][9] == DBNull.Value) ? string.Empty : dataTable.Rows[i][9].ToString();
                model.LienKet = (dataTable.Rows[i][10] == DBNull.Value) ? string.Empty : dataTable.Rows[i][10].ToString();
                model.Tag = (dataTable.Rows[i][11] == DBNull.Value) ? string.Empty : dataTable.Rows[i][11].ToString();
                model.NgayCapNhat = (dataTable.Rows[i][12] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][12];
                model.TrangThai = (dataTable.Rows[i][13] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][13]);
                model.HienthiPanoramanho = (dataTable.Rows[i][14] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][14]);
                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Remove panorama nho
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int RemovePanorama(ModelVYC06 model)
        {
            var query = "UPDATE [VYC-06] SET [HienthiPanoramanho] = @HienthiPanoramanho WHERE Id = @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@id", SqlDbType.Int).Value = model.Id;
            sqlCommand.Parameters.Add("@HienthiPanoramanho", SqlDbType.Bit).Value = model.HienthiPanoramanho;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
        }

        /// <summary>
        /// Get list panorama nho cua bai viet
        /// </summary>
        /// <returns></returns>
        public List<ModelVYC06> GetListPanoramaNho(int hienthi, int trangthai)
        {
            var list = new List<ModelVYC06>();
            try
            {
                var query = "SELECT * FROM [VYC-06] WHERE HienthiPanoramanho = @hienthipanoramanho and TrangThai = @trangthai Order by NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@hienthipanoramanho", SqlDbType.Bit).Value = hienthi;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    var model = new ModelVYC06();

                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.IdSubLoaiChuyenMuc = Convert.ToInt32(reader["IdSubLoaiChuyenMuc"]);
                    model.IdLoaiChuyenMuc = Convert.ToInt32(reader["IdLoaiChuyenMuc"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.LuotXem = Convert.ToInt32(reader["LuotXem"]);
                    model.TacGia = reader["TacGia"].ToString();
                    model.LienKet = reader["LienKet"].ToString();
                    model.Tag = reader["Tag"].ToString();

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally 
            {
                CloseConnection();
            }

        }

        public List<ModelVYC06> GetByUniqueId(string uniqueid, int trangthai)
        {
            var list = new List<ModelVYC06>();
            try
            {
                var query = "SELECT * FROM [VYC-06] WHERE UniqueId = @uniqueid and TrangThai = @trangthai Order by NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@uniqueid", SqlDbType.VarChar).Value = uniqueid;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    var model = new ModelVYC06();

                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.IdSubLoaiChuyenMuc = Convert.ToInt32(reader["IdSubLoaiChuyenMuc"]);
                    model.IdLoaiChuyenMuc = Convert.ToInt32(reader["IdLoaiChuyenMuc"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.LuotXem = Convert.ToInt32(reader["LuotXem"]);
                    model.TacGia = reader["TacGia"].ToString();
                    model.LienKet = reader["LienKet"].ToString();
                    model.Tag = reader["Tag"].ToString();
                    model.NgayCapNhat = (DateTime) reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }

        }

        public List<ModelVYC06> GetAllByView()
        {
            var list = new List<ModelVYC06>();
            try
            {
                var query = "SELECT * FROM [VYC-06] WHERE TrangThai = @trangthai Order by LuotXem DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = 1;
                var reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    var model = new ModelVYC06();

                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.IdSubLoaiChuyenMuc = Convert.ToInt32(reader["IdSubLoaiChuyenMuc"]);
                    model.IdLoaiChuyenMuc = Convert.ToInt32(reader["IdLoaiChuyenMuc"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.LuotXem = Convert.ToInt32(reader["LuotXem"]);
                    model.TacGia = reader["TacGia"].ToString();
                    model.LienKet = reader["LienKet"].ToString();
                    model.Tag = reader["Tag"].ToString();
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }

        }

        public List<ModelVYC06> GetByView()
        {
            var list = new List<ModelVYC06>();
            try
            {
                var query = "SELECT TOP 10 * FROM [VYC-06] WHERE TrangThai = @trangthai Order by LuotXem DESC";
                var sqlCommand = new SqlCommand(query, connection);                
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = 1;
                var reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    var model = new ModelVYC06();

                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.IdSubLoaiChuyenMuc = Convert.ToInt32(reader["IdSubLoaiChuyenMuc"]);
                    model.IdLoaiChuyenMuc = Convert.ToInt32(reader["IdLoaiChuyenMuc"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.LuotXem = Convert.ToInt32(reader["LuotXem"]);
                    model.TacGia = reader["TacGia"].ToString();
                    model.LienKet = reader["LienKet"].ToString();
                    model.Tag = reader["Tag"].ToString();
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }

        }

        public List<ModelVYC06> GetByNewest()
        {
            var list = new List<ModelVYC06>();
            try
            {
                var query = "SELECT TOP 10 * FROM [VYC-06] WHERE TrangThai = @trangthai Order by NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = 1;
                var reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    var model = new ModelVYC06();

                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.IdSubLoaiChuyenMuc = Convert.ToInt32(reader["IdSubLoaiChuyenMuc"]);
                    model.IdLoaiChuyenMuc = Convert.ToInt32(reader["IdLoaiChuyenMuc"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.LuotXem = Convert.ToInt32(reader["LuotXem"]);
                    model.TacGia = reader["TacGia"].ToString();
                    model.LienKet = reader["LienKet"].ToString();
                    model.Tag = reader["Tag"].ToString();
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }

        }

        /// <summary>
        /// Get by quang cao
        /// </summary>
        /// <returns></returns>
        public List<ModelVYC06> GetByQuangcao()
        {
            var list = new List<ModelVYC06>();
            try
            {
                var query = "SELECT TOP 10 * FROM [VYC-06] WHERE TrangThai = @trangthai Order by NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = 1;
                var reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    var model = new ModelVYC06();

                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.IdSubLoaiChuyenMuc = Convert.ToInt32(reader["IdSubLoaiChuyenMuc"]);
                    model.IdLoaiChuyenMuc = Convert.ToInt32(reader["IdLoaiChuyenMuc"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.LuotXem = Convert.ToInt32(reader["LuotXem"]);
                    model.TacGia = reader["TacGia"].ToString();
                    model.LienKet = reader["LienKet"].ToString();
                    model.Tag = reader["Tag"].ToString();
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }

        }

        public List<ModelVYC06> GetByLoaiCm(int idLoaiCM, int trangthai)
        {
            var list = new List<ModelVYC06>();
            try
            {
                var query = "SELECT * FROM [VYC-06] WHERE IdLoaiChuyenMuc = @idloaicm and TrangThai = @trangthai Order by NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@idloaicm", SqlDbType.Int).Value = idLoaiCM;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    var model = new ModelVYC06();

                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.IdSubLoaiChuyenMuc = Convert.ToInt32(reader["IdSubLoaiChuyenMuc"]);
                    model.IdLoaiChuyenMuc = Convert.ToInt32(reader["IdLoaiChuyenMuc"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.LuotXem = Convert.ToInt32(reader["LuotXem"]);
                    model.TacGia = reader["TacGia"].ToString();
                    model.LienKet = reader["LienKet"].ToString();
                    model.Tag = reader["Tag"].ToString();
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }

        }

        public List<ModelVYC06> GetBySubLoaiCm(int idSubLoaiCM, int trangthai)
        {
            var list = new List<ModelVYC06>();
            try
            {
                var query = "SELECT * FROM [VYC-06] WHERE IdSubLoaiChuyenMuc = @idsubloaicm and TrangThai = @trangthai Order by NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@idsubloaicm", SqlDbType.Int).Value = idSubLoaiCM;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    var model = new ModelVYC06();

                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.IdSubLoaiChuyenMuc = Convert.ToInt32(reader["IdSubLoaiChuyenMuc"]);
                    model.IdLoaiChuyenMuc = Convert.ToInt32(reader["IdLoaiChuyenMuc"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.LuotXem = Convert.ToInt32(reader["LuotXem"]);
                    model.TacGia = reader["TacGia"].ToString();
                    model.LienKet = reader["LienKet"].ToString();
                    model.Tag = reader["Tag"].ToString();
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }

        }

        public List<ModelVYC06> GetBySubLoaiCmMoinhat(int idSubLoaiCM, int trangthai)
        {
            var list = new List<ModelVYC06>();
            try
            {
                var query = "SELECT * FROM [VYC-06] WHERE IdSubLoaiChuyenMuc = @idsubloaicm and TrangThai = @trangthai Order by NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@idsubloaicm", SqlDbType.Int).Value = idSubLoaiCM;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    var model = new ModelVYC06();

                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.IdSubLoaiChuyenMuc = Convert.ToInt32(reader["IdSubLoaiChuyenMuc"]);
                    model.IdLoaiChuyenMuc = Convert.ToInt32(reader["IdLoaiChuyenMuc"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.LuotXem = Convert.ToInt32(reader["LuotXem"]);
                    model.TacGia = reader["TacGia"].ToString();
                    model.LienKet = reader["LienKet"].ToString();
                    model.Tag = reader["Tag"].ToString();
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }

        }

        public List<ModelVYC06> GetBySubLoaiCmNoibat(int idSubLoaiCM, int trangthai)
        {
            var list = new List<ModelVYC06>();
            try
            {
                var query = "SELECT * FROM [VYC-06] WHERE IdSubLoaiChuyenMuc = @idsubloaicm and TrangThai = @trangthai Order by LuotXem DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@idsubloaicm", SqlDbType.Int).Value = idSubLoaiCM;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                var reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    var model = new ModelVYC06();

                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.IdSubLoaiChuyenMuc = Convert.ToInt32(reader["IdSubLoaiChuyenMuc"]);
                    model.IdLoaiChuyenMuc = Convert.ToInt32(reader["IdLoaiChuyenMuc"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.LuotXem = Convert.ToInt32(reader["LuotXem"]);
                    model.TacGia = reader["TacGia"].ToString();
                    model.LienKet = reader["LienKet"].ToString();
                    model.Tag = reader["Tag"].ToString();
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }

        }

        public List<ModelVYC06> GetLichkhoihanh(int idSubLoaiCM, string tieude, int trangthai)
        {
            var list = new List<ModelVYC06>();
            try
            {
                var query = "SELECT * FROM [VYC-06] WHERE IdSubLoaiChuyenMuc = @idsubloaicm and UniqueId like @tieude and TrangThai = @trangthai Order by NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@idsubloaicm", SqlDbType.Int).Value = idSubLoaiCM;
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;
                sqlCommand.Parameters.Add("@tieude", SqlDbType.VarChar).Value = tieude;
                var reader = sqlCommand.ExecuteReader();                               
                while (reader.Read())
                {
                    var model = new ModelVYC06();

                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.IdSubLoaiChuyenMuc = Convert.ToInt32(reader["IdSubLoaiChuyenMuc"]);
                    model.IdLoaiChuyenMuc = Convert.ToInt32(reader["IdLoaiChuyenMuc"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.LuotXem = Convert.ToInt32(reader["LuotXem"]);
                    model.TacGia = reader["TacGia"].ToString();
                    model.LienKet = reader["LienKet"].ToString();
                    model.Tag = reader["Tag"].ToString();
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                reader.Close();
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }

        }


        public List<ModelVYC06> GetByTag(string key, int trangthai)
        {
            var list = new List<ModelVYC06>();
            try
            {
                var query = "SELECT * FROM [VYC-06] WHERE Tag like @tag and TrangThai = @trangthai Order by NgayCapNhat DESC";
                var sqlCommand = new SqlCommand(query, connection);
                sqlCommand.Parameters.Add("@tag", SqlDbType.NVarChar).Value = "%" + key + "%";
                sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = trangthai;                
                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    var model = new ModelVYC06();

                    model.Id = Convert.ToInt32(reader["Id"]);
                    model.IdSubLoaiChuyenMuc = Convert.ToInt32(reader["IdSubLoaiChuyenMuc"]);
                    model.IdLoaiChuyenMuc = Convert.ToInt32(reader["IdLoaiChuyenMuc"]);
                    model.TieuDe = reader["TieuDe"].ToString();
                    model.UniqueId = reader["UniqueId"].ToString();
                    model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                    model.TomTat = reader["TomTat"].ToString();
                    model.NoiDung = reader["NoiDung"].ToString();
                    model.LuotXem = Convert.ToInt32(reader["LuotXem"]);
                    model.TacGia = reader["TacGia"].ToString();
                    model.LienKet = reader["LienKet"].ToString();
                    model.Tag = reader["Tag"].ToString();
                    model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];

                    list.Add(model);
                }
                reader.Close();
                return list;
            }
            catch (Exception)
            {
                CloseConnection();
                throw;
            }
            finally
            {
                CloseConnection();
            }

        }

    }
}